Load data

raw_df = 
  read_excel("data/pce_by_state.xlsx", sheet = "Table 1", range = "A4:F63", col_names = FALSE)

colnames(raw_df) = c("state", "2018", "2019", "2020", "2019_change", "2020_change")

Clean data

pce_state_df = 
  raw_df %>% 
  filter(!(state %in% c("United States", "New England", "Mideast", "Great Lakes", "Plains", "Southeast", "Southwest", "Rocky Mountain", "Far West"))) %>% 
  mutate(
    code = c("CT", "ME", "MA", "NH", "RI", "VT", "DE", "DC", "MD", "NJ", "NY", "PA", "IL", "IN", "MI", "OH", "WI", "IA", "KS", "MN", "MO", "NE", "ND", "SD", "AL", "AR", "FL", "GA", "KY", "LA", "MS", "NC", "SC", "TN", "VA", "WV", "AZ", "NM", "OK", "TX", "CO", "ID", "MT", "UT", "WY", "AK", "CA", "HI", "NV", "OR", "WA")
  ) %>% 
  select(-"2019_change", -"2020_change") %>% 
  pivot_longer(
    "2018":"2020",
    names_to = "year",
    values_to = "pce"
  ) %>% 
  mutate(
    year = as.numeric(year),
  )

Draw map

pce_state_20_df =
  pce_state_df %>% 
  filter(year == "2020")

g <- list(
  scope = 'usa',
  projection = list(type = 'albers usa'),
  showlakes = TRUE,
  lakecolor = toRGB('white')
)
fig <- plot_geo(pce_state_20_df, locationmode = 'USA-states')
fig <- fig %>% add_trace(
    z = ~pce, text = ~state, locations = ~code,
    color = ~pce, colorscale = list(c(0, 0.4, 1), c("#ecf6ff", "478fcc", "#004a88"))
  )
fig <- fig %>% colorbar(title = "PCE")
fig <- fig %>% layout(
    title = 'Per Capita Personal Consumption Expenditures by State, 2020)',
    geo = g
  )

fig